﻿using MMS.Api.Helper;
using MMS.Model.FormatModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.WebPages;

namespace MMS.Api.Areas.Admin.Controllers
{
    using MMS.Model.FormatModel;
    using MMS.Api.Helper;
    using MMS.Model;
    public class MembershipAnalysisController : BaseController
    {   
        #region 1.0 会员消费分析 ，//http://localhost:16824/api/MembershipAnalysis/GetConsumption
        public List<class_Consumption> GetConsumption()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Consumption>("select m.MId as Id,m.MName as Name,COUNT(t.TId) as Frequency,MAX(t.TActualPay)as MaxMonetary,AVG(t.TActualPay)as AVGMonetary,COUNT(c.GCId) as AllCategory,MAX(t.TTime) as dataTime from MemberInfo as m,TradeInfo as t,(select g.GCId,t.TId from GoodsInfo as g,(select s.SIGId ,t.TId from TradeInfo as t,SaleItem as s where t.TId=s.SITId) as t where t.SIGId=g.GId) as c where m.MId =t.TMId and t.TId = c.TId group by m.MId,m.MName").ToList();
            return list;
        }

        /// <summary>
        /// 对单个会员的数据进行分析
        ///  http://localhost:16824/api/MembershipAnalysis/GetPersonConsumption
        /// </summary>
        /// <param name="Id">会员Id</param>
        /// <returns></returns>
        public List<class_Consumption> GetPersonConsumption(string Id)
        {
            int id = Convert.ToInt32(Id);
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Consumption>("select m.MId as Id,m.MName as Name,COUNT(t.TId) as Frequency,MAX(t.TActualPay)as MaxMonetary,AVG(t.TActualPay)as AVGMonetary,COUNT(c.GCId) as AllCategory ,t.TTime as dataTime from MemberInfo as m,TradeInfo as t,(select g.GCId,t.TId from GoodsInfo as g,(select s.SIGId ,t.TId from TradeInfo as t,SaleItem as s where t.TId=s.SITId) as t where t.SIGId=g.GId) as c where m.MId =t.TMId and t.TId = c.TId and m.MId='" + id + "' group by m.MId,m.MName,t.TTime").ToList();
            return list;
        }
        /// <summary>
        /// 会员年度分析
        /// http://localhost:16824/api/MembershipAnalysis/GetAnnualConsumption
        /// </summary>
        /// <returns></returns>

        public List<class_Consumption> GetAnnualConsumption()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Consumption>("select COUNT(t.TId) as Frequency,MAX(t.TActualPay)as MaxMonetary,AVG(t.TActualPay)as AVGMonetary,SUM(t.TActualPay) as AllMonetary,COUNT(c.GCId) as AllCategory ,convert(varchar(4),t.TTime,111) as strTime from MemberInfo as m,TradeInfo as t,(select g.GCId,t.TId from GoodsInfo as g,(select s.SIGId ,t.TId from TradeInfo as t,SaleItem as s where t.TId=s.SITId) as t where t.SIGId=g.GId) as c where m.MId =t.TMId and t.TId = c.TId group by convert(varchar(4),t.TTime,111)").ToList();
            return list;

        }
        /// <summary>
        /// 会员月度分析
        /// http://localhost:16824/api/MembershipAnalysis/GetMonthlyConsumption
        /// </summary>
        /// <returns></returns>
        public List<class_Consumption> GetMonthlyConsumption()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Consumption>("select COUNT(t.TId) as Frequency,MAX(t.TActualPay)as MaxMonetary,AVG(t.TActualPay)as AVGMonetary,SUM(t.TActualPay) as AllMonetary,COUNT(c.GCId) as AllCategory ,convert(varchar(7),t.TTime,111) as strTime from MemberInfo as m,TradeInfo as t,(select g.GCId,t.TId from GoodsInfo as g,(select s.SIGId ,t.TId from TradeInfo as t,SaleItem as s where t.TId=s.SITId) as t where t.SIGId=g.GId) as c where m.MId =t.TMId and t.TId = c.TId group by convert(varchar(7),t.TTime,111)").ToList();
            return list;
        }

        #endregion

        #region 2.0 会员地域分析，获取会员地址，并对同一地址的进行求和 //http://localhost:16824/api/MembershipAnalysis/GetMAddressAndGroupSumGroupByMAddress
        public List<class_Region> GetMAddressAndGroupSumGroupByMAddress()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Region>("select MAddress,COUNT(MAddress) as GroupSum from MemberInfo group by MAddress").ToList();
            return list;
        }
        #endregion

        #region 3.0忠诚度分析，
        public List<class_Loyalty> GetLoyalty()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Loyalty>("select m.MName as MName,m.MId as MId ,convert(nvarchar(7),t.TTime,111) as yue,MAX(t.TTime) as ttime,COUNT(t.TId) as cishu from MemberInfo as m,TradeInfo as t group by m.MName,MId,convert(nvarchar(7),t.TTime,111)order by convert(nvarchar(7),t.TTime,111) desc").ToList();
            return list;
        }
        /// <summary>
        /// 
        ///  http://localhost:16824/api/MembershipAnalysis/GetLoyalty1
        /// </summary>
        /// <param name="id">页码</param>
        /// <param name="id1">页容量</param>
        /// <returns></returns>
        public PageData<class_Loyalty> GetLoyalty1(string id, string id1)
        {
            int pageIndex = id.AsInt();
            int pageSize = id1.AsInt();
            int skip = (pageIndex - 1) * pageSize;
            string sql = "select top " + pageSize + " MName,MId,yue,ttime,cishu from (select m.MName as MName,m.MId as MId,convert(nvarchar(7),t.TTime,111) as yue,MAX(t.TTime) as ttime,COUNT(t.TId) as cishu,ROW_NUMBER()over (order by convert(nvarchar(7),t.TTime,111) desc) as [row_] from MemberInfo as m,TradeInfo as t where m.MId=t.TMId group by m.MName,MId,convert(nvarchar(7),t.TTime,111)) as a where [row_]>" + skip + "";

            Model.FormatModel.PageData<class_Loyalty> pageData = new Model.FormatModel.PageData<class_Loyalty>();
            var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_Loyalty>(sql).ToList();
            pageData.rows = list;
            string sql1 = "select m.MName,convert(nvarchar(7),t.TTime,111) as yue,MAX(t.TTime) as ttime,COUNT(t.TId) as cishu from MemberInfo as m,TradeInfo as t where m.MId=t.TMId group by m.MName,convert(nvarchar(7),t.TTime,111)order by convert(nvarchar(7),t.TTime,111) desc";

            var list1 = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_Loyalty>(sql1).ToList();
            pageData.total = list1.Count();

            return pageData;
        }

        /// <summary>
        /// http://localhost:16824/api/MembershipAnalysis/GetMemberLoyalty
        /// </summary>
        /// <param name="id">会员Id</param>
        /// <returns></returns>
        public List<class_Loyalty> GetMemberLoyalty(int id)
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Loyalty>("select m.MName as MName,convert(nvarchar(7),t.TTime,111) as yue, COUNT(t.TId) as cishu from MemberInfo as m,TradeInfo as t where m.MId=t.TMId and MId=" + id + " group by MName,convert(nvarchar(7),t.TTime,111)").ToList();
            return list;
        }
        #endregion

        #region 4.礼品兑换分析 ，//http://localhost:16824/api/MembershipAnalysis/GetExchangeGift

        public List<class_ExchangeGift> GetExchangeGift()
        {
            var list = OpeCur.BLLSession.Exchange.SqlQuery<class_ExchangeGift>("select g.GName as Gname, COUNT( g.GName) as 'Count' from Exchange as e,GoodsInfo as g where e.EGId=g.GId group by g.GName order by COUNT(g.GName) desc").ToList();
            return list;

        }
        #endregion

        #region 5.会员等级分析,// http://localhost:16824/api/MembershipAnalysis/GetLevel
        public List<class_Level> GetLevel()
        {
            var list = OpeCur.BLLSession.Level.SqlQuery<class_Level>("select l.LName as LName,m.tid as Tid,MAX(m.MaxMonetary) as MaxMonetary,AVG(m.AVGMonetary) as AVGMonetary from Level as l, (select m.MLId,Count(t.TId) as Tid,MAX(t.TActualPay) as MaxMonetary,AVG(t.TActualPay) as AVGMonetary from MemberInfo as m,TradeInfo as t where m.MId=TMId group by m.MLId) as m where m.MLId=l.LId group by l.LName, TId order by l.LName").ToList();
            return list;
        }
        #endregion

        #region 7.会员流失预警
        /// <summary>
        /// 获取三个月以上半年以内未消费者数据 , http://localhost:16824/api/MembershipAnalysis/GetLoss
        /// </summary>
        /// <returns></returns>
        public List<class_Loss> GetLoss()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Loss>("select m.MName,MAX(t.TTime) as ttime from MemberInfo as m,TradeInfo as t where m.MId=t.TMId and (DATEDIFF(MONTH,ttime,GETDATE())>3)and  (DATEDIFF(MONTH,ttime,GETDATE())<6)group by m.MName").ToList();
            return list;
        }
        /// <summary>
        /// 获取半年以上一年以内未消费者数据
        /// http://localhost:16824/api/MembershipAnalysis/GetLoss1
        /// </summary>
        /// <returns></returns>
        public List<class_Loss> GetLoss1()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Loss>("select m.MName,MAX(t.TTime) as ttime from MemberInfo as m,TradeInfo as t where m.MId=t.TMId and (DATEDIFF(MONTH,ttime,GETDATE())>6) and  (DATEDIFF(MONTH,ttime,GETDATE())<12) group by m.MName").ToList();
            return list;
        }
        /// <summary>
        /// 获取一年以上未消费者数据
        /// http://localhost:16824/api/MembershipAnalysis/GetLoss2
        /// </summary>
        /// <returns></returns>
        public List<class_Loss> GetLoss2()
        {
            var list = OpeCur.BLLSession.MemberInfo.SqlQuery<class_Loss>("select m.MName,MAX(t.TTime) as ttime from MemberInfo as m,TradeInfo as t where m.MId=t.TMId and  (DATEDIFF(MONTH,ttime,GETDATE())>12) group by m.MName").ToList();
            return list;
        }
        #endregion

        #region 8.0 加载 会员群体分析 图表
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">startdate</param>
        /// <param name="id1">enddate</param>
        /// <returns></returns>
        
        public List<class_Colony> GetColony(string id, string id1)
        {
            if (id.Length == 14)
            {
                DateTime startdate = DateTime.ParseExact(id, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                id = startdate.ToString();
                DateTime enddate = DateTime.ParseExact(id1, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                id1 = enddate.ToString();
            }
            string sql = "use MMS;select Gender.Number as GenderNumber,Gender.Gender as Gender,Age.Number as AgeNumber,Age.Age as Age,Occupation.Number as OccupationNumber,Occupation.Occupation as Occupation from(select COUNT(*) as Number,M.MGender	as Gender from TradeInfo as T left join	MemberInfo as M	on T.TMId=M.MId	where T.TTime between '" + id + "' and '" + id1 + "' group by M.MGender ) as Gender full join (select COUNT(*) as number	,datediff(year,M.MBirthday,getdate())as Age	from TradeInfo as T left join MemberInfo as M on T.TMId=M.MId where T.TTime between '" + id + "' and '" + id1 + "' group by datediff(year,M.MBirthday,getdate()) ) as Age on Gender.Gender=CAST(Age.Age as varchar(20)) full join (select COUNT(*) as number ,M.MOccupation	as Occupation from 	TradeInfo as T left join MemberInfo as M on T.TMId=M.MId where 	T.TTime between '" + id + "' and '" + id1 + "' group by M.MOccupation ) as Occupation on Gender.Gender=Occupation.Occupation ";
            var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_Colony>(sql).ToList();
            return list;
        }
        #endregion

        #region 9.0 加载 会员购物时段分析 数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">startdate</param>
        /// <param name="id1">enddate</param>
        /// <param name="id2">category</param>
        /// <param name="id3">less</param>
        /// <param name="id4">greater</param>
        /// <returns></returns>
        public List<class_Time> GetTime(string id, string id1, string id2, string id3, string id4)
        {
            string category="DateDiff(YEAR,MBirthday,getdate())>=21 and DateDiff(YEAR,MBirthday,getdate())<=21";
            
            switch(id2)
            {
                case "按性别":
                    category ="MGender='" + id3 + "'";break;
                case "按年龄":
                    category =  "DateDiff(YEAR,MBirthday,getdate())>=" + id3 + " and DateDiff(YEAR,MBirthday,getdate())<=" + id4; break;
                case "按消费额度":
                    category = "MConsumption>=" + id3 + " and MConsumption<=" + id4 ; break;
            }
            if (id.Length == 14)
            {
                DateTime startdate = DateTime.ParseExact(id, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                id = startdate.ToString();
                DateTime enddate = DateTime.ParseExact(id1, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                id1 = enddate.ToString();
            }
            string sql = "use MMS;select COUNT(*) as Number ,DatePart(HOUR,TTime) as SaleTime from TradeInfo where TMId in (select MId from MemberInfo where " + category + ") and TTime between '" + id + "' and '" + id1 + "' group by DatePart(HOUR,TTime)";
            var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_Time>(sql).ToList();
            return list;
        }
        #endregion

        #region 10.0 加载 会员购物金额分析 图表
       
        #endregion

        #region 11.0 加载 会员购物品类分析 图表
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id">startdate</param>
        /// <param name="id1">enddate</param>
        /// <param name="id2">category</param>
        /// <param name="id3">less</param>
        /// <param name="id4">greater</param>
        /// <returns></returns>
        public List<class_Member_category> GetCategory(string id, string id1, string id2, string id3, string id4)
        {
            string category = "DateDiff(YEAR,MBirthday,getdate())>=21 and DateDiff(YEAR,MBirthday,getdate())<=21";

            switch (id2)
            {
                case "按性别":
                    category = "MGender='" + id3 + "'"; break;
                case "按年龄":
                    category = "DateDiff(YEAR,MBirthday,getdate())>=" + id3 + " and DateDiff(YEAR,MBirthday,getdate())<=" + id4; break;
                case "按消费额度":
                    category = "MConsumption>=" + id3 + " and MConsumption<=" + id4; break;
            }
            if (id.Length == 14)
            {
                DateTime startdate = DateTime.ParseExact(id, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                id = startdate.ToString();
                DateTime enddate = DateTime.ParseExact(id1, "yyyyMMddHHmmss", System.Globalization.CultureInfo.CurrentCulture);
                id1 = enddate.ToString();
            }
            string sql = "use MMS;select COUNT(*) as Number,C.CName as CategoryName from SaleItem as S left join GoodsInfo as G on	S.SIGId=G.GId left join	Category as C on G.GCId=C.CId where SIMId in (select MId from MemberInfo where " + category + ") and SITime between '" + id + "' and '" + id1 + "' group by C.CName ";
            var list = OpeCur.BLLSession.GoodsInfo.SqlQuery<class_Member_category>(sql).ToList();
            return list;
        }
        #endregion
    }
}
